﻿using System;
using System.Data;
using System.Data.SqlServerCe;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using Common.Entities;

namespace Common.Models
{
    public class VisitRepository
    {
        public List<Visit> GetVisitsByDateWork(DateTime dateWork)
        {
            try
            {
                using (SqlCeConnection connection = WorkDataBase.CreateConnection())
                {
                    List<Visit> visits = new List<Visit>(5);
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                    string sqlQuery =
                        "SELECT IdVisit, DateVisit, BuyPointId, TimeEndVisit, Coord, IsExport FROM Visits" +
                        " where DateVisit between @startDateVisits and @endDateVisits";
                    using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                    {
                        DateTime endDate = (dateWork.AddHours(23).AddMinutes(59).AddSeconds(59)).Date;
                        command.Parameters.Add("@startDateVisits", dateWork.Date);
                        command.Parameters.Add("@endDateVisits", endDate);
                        using (SqlCeDataReader dr = command.ExecuteReader())
                        {
                            Visit visit;
                            int idVisit = dr.GetOrdinal("IdVisit");
                            int isReadOnly = dr.GetOrdinal("IsExport");
                            int timeEndVisit = dr.GetOrdinal("TimeEndVisit");
                            int buyPointId = dr.GetOrdinal("BuyPointId");
                            while (dr.Read())
                            {
                                visit = new Visit();
                                visit.IsOrder = false;
                                visit.IdVisit = dr.GetInt32(idVisit);
                                visit.IsVisit = dr.IsDBNull(timeEndVisit) ? false : true;
                                visit.IsReadOnly = dr.GetBoolean(isReadOnly);
                                visit.TimeEndVisit = dr.IsDBNull(timeEndVisit)
                                                         ? ""
                                                         : dr.GetValue(timeEndVisit).ToString();
                                visit.BuyPoint = DataManager.GetInstance().BuyPoints.GetBuyPointById(dr.GetInt32(buyPointId));
                                visit.Orders = DataManager.GetInstance().Orders.GetOrdersByVisit(visit.IdVisit);
                                if (visit.Orders.Count > 0)
                                    visit.IsOrder = true;
                                visits.Add(visit);
                            }
                            return visits;
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void CreateVisit (Visit visit)
        {
            try
            {
                using (SqlCeConnection connection = WorkDataBase.CreateConnection())
                {
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                    string sqlQuery = "insert INTO Visits (DateVisit, BuyPointId) values (@DateVisit, @BuyPointId)";
                    using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                    {
                        command.Parameters.Add("@DateVisit", SqlDbType.DateTime).Value = visit.DateVisit;
                        command.Parameters.Add("@BuyPointId", SqlDbType.Int).Value = visit.BuyPoint.IdBuyPoint;
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
        }

        public void UpdateVisit(Visit visit)
        {
            try
            {
                using (SqlCeConnection connection = WorkDataBase.CreateConnection())
                {
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                    string sqlQuery = "Update Visits set "+
                        "TimeEndVisit = @TimeEndVisit, IsExport = @IsExport where idVisit = @IdVisit";
                    using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                    {
                        command.Parameters.Add("@TimeEndVisit", SqlDbType.DateTime).Value = visit.TimeEndVisit;
                        command.Parameters.Add("@IsExport", SqlDbType.Bit).Value = visit.IsReadOnly;
                        command.Parameters.Add("@IdVisit", SqlDbType.Int).Value = visit.IdVisit;
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteVisit (int id)
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                string sqlQuery = "Delete from Visits where IdVisit = @IdVisit";
                using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                {
                    command.Parameters.Add("@IdVisit", id);
                    command.ExecuteNonQuery();
                }
            }
        }

        public void LoadFromDate (DateTime dateTime)
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                string sqlQuery =
                        "insert INTO Visits (DateVisit, BuyPointId) SELECT '" + dateTime.Date +
                        "' AS DateVisit, BuyPointId FROM GraphicsVisits WHERE " + dateTime.DayOfWeek.ToString() +
                        " = 1";
                    using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
            }
        }
    }

